package railRoad.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import railRoad.Station;
import railRoad.dao.interfaces.IDAO;
import railRoad.db.DBConectionPool;
import railRoad.exception.DBException;

/**
  * Implementation of {@link#IDAO} for {@link#Station}
 * 
 * @see IDAO
 * @see Station
 * @author Rukin
 *
 */
public class StationDAO implements IDAO<Station> {

	DBConectionPool conectionPool = DBConectionPool.getInstance();

	public Station select(int id) throws DBException {
		Station station = null;
		Connection connection = null;
		try {
			connection = conectionPool.getConnection();
			connection.setAutoCommit(false);

			Statement statement = connection.createStatement();
			ResultSet resultSet = statement
					.executeQuery("SELECT * FROM STATION WHERE STATION_ID  = " + id);

			while (resultSet.next()) {
				int sId = resultSet.getInt("STATION_ID");
				float altitude = resultSet.getFloat("ALTITUDE");
				float longitude = resultSet.getFloat("LONGITUDE");
				String name = resultSet.getString("NAME");

				station = new Station(sId, name, altitude, longitude);
			}
		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return station;
	}// select

	public int update(Station station) throws DBException {

		Connection connection = null;

		int rezult = -1;
		try {
			connection = conectionPool.getConnection();
			connection.setAutoCommit(false);

			PreparedStatement preparedStatement = connection
					.prepareStatement("UPDATE STATION st SET st.NAME = ?, "
							+ "st.ALTITUDE = ?, st.LONGITUDE = ? "
							+ "WHERE st.STATION_ID = ?");

			preparedStatement.setString(1, station.getStationName());
			preparedStatement.setFloat(2, station.getAltitude());
			preparedStatement.setFloat(3, station.getLongitude());
			preparedStatement.setInt(4, station.getStation_id());

			rezult = preparedStatement.executeUpdate();

			connection.commit();
		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return rezult;
	}// update

	public int insert(Station station, int route_id) throws DBException {
		Connection connection = null;
		int result = -1;
		try {
			connection = conectionPool.getConnection();
			connection.setAutoCommit(false);

			PreparedStatement preparedStatement = connection
					.prepareStatement("INSERT INTO STATION( ROUTE_ID , NAME , ALTITUDE , LONGITUDE ) VALUES (?,?,?,?)");

			preparedStatement.setInt(1, route_id);
			preparedStatement.setString(2, station.getStationName());
			preparedStatement.setFloat(3, station.getAltitude());
			preparedStatement.setFloat(4, station.getLongitude());

			result = preparedStatement.executeUpdate();

			connection.commit();

			preparedStatement = connection
					.prepareStatement("SELECT MAX(STATION_ID) as ID FROM STATION WHERE ROUTE_ID = ?");
			preparedStatement.setInt(1, route_id);

			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				station.setStation_id(resultSet.getInt("ID"));
			}
		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally

		return result;
	}

	public int delete(Station removedObject) throws DBException {
		Connection connection = null;
		
		int result = -1;
		
		try {
			connection = conectionPool.getConnection();
			connection.setAutoCommit(false);

			PreparedStatement preparedStatement = connection
					.prepareStatement("DELETE FROM STATION WHERE STATION.STATION_ID = ?");

			preparedStatement.setInt(1, removedObject.getStation_id());

			result = preparedStatement.executeUpdate();

			connection.commit();
		} catch (SQLException e) {
			throw new DBException(e.getMessage());
		} finally {
			conectionPool.close(connection);
		}// finally
		
		return result;
	}//delete

	public Station select(String field) throws DBException {
		// TODO Auto-generated method stub
		return null;
	}

}
